package day03;

import org.junit.Test;
import pojo.Brand;
import utilspackage.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @author Xiao
 * @date 2021/8/20
 */
public class BrnadTest {
    /**
     * 完成商品品牌数据的增删改查操作
     * - 查询：查询所有数据
     * - 查询：根据id查询
     * - 添加：添加品牌
     * - 修改：根据id修改
     * - 删除：根据id删除
     */
    /**查询：查询所有数据*/
    @Test
    public void testQueryAllData() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT * FROM tb_brand";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Brand> list = new ArrayList<>();
        while (rs.next()) {
            int id = rs.getInt(1);
            String brandName = rs.getString(2);
            String companyName = rs.getString(3);
            int ordered = rs.getInt(4);
            String description = rs.getString(5);
            int status = rs.getInt(6);
            list.add(new Brand(id, brandName, companyName, ordered, description, status));
        }
        rs.close();
        pstmt.close();
        for (Brand brand : list) {
            System.out.println(brand);
        }
    }

    /**查询单个数据*/
    @Test
    public void testFindById() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT * FROM tb_brand WHERE id = 1";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Brand> list = new ArrayList<>();
        while (rs.next()) {
            int id = rs.getInt(1);
            String brandName = rs.getString(2);
            String companyName = rs.getString(3);
            int ordered = rs.getInt(4);
            String description = rs.getString(5);
            int status = rs.getInt(6);
            list.add(new Brand(id, brandName, companyName, ordered, description, status));
        }
        rs.close();
        pstmt.close();
        conn.close();
        for (Brand brand : list) {
            System.out.println(brand);
        }
    }

    /**添加：添加品牌*/
    @Test
    public void testInsertData() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "INSERT INTO tb_brand VALUES(4, '百度', '百度科技', 150, '百度一下，生活更好', 1)";
        Statement stmt = conn.createStatement();
        int count = stmt.executeUpdate(sql);
        System.out.println(count);
        stmt.close();
        conn.close();
    }

    /**修改：根据id修改*/
    @Test
    public void testUpdateData() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "UPDATE tb_brand SET description = '为发烧而生' WHERE id = 3";
        Statement stmt = conn.createStatement();
        int count = stmt.executeUpdate(sql);
        System.out.println(count);
        stmt.close();
        conn.close();
    }

    /**删除：根据id删除*/
    @Test
    public void testDeleteData() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "DELETE FROM tb_brand WHERE ID = 4";
        Statement stmt = conn.createStatement();
        int count = stmt.executeUpdate(sql);
        System.out.println(count);
        stmt.close();
        conn.close();
    }
}
